package ca.cbc.medialib.service;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;
import java.util.List;

import org.hibernate.Session;
import org.hibernate.criterion.Restrictions;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import ca.cbc.medialib.entity.Category;
import ca.cbc.medialib.entity.FailedMedia;
import ca.cbc.medialib.entity.Media;
import ca.cbc.medialib.entity.Sponsor;
import ca.cbc.medialib.util.HibernateUtil;

/**
 * =============================================
 * Copyright (c) 2011 Canadian Broadcasting Corporation.
 * All rights reserved.
 * <p/>
 * ca.cbc.medialib.service.MediaStorageServiceImpl.java
 * =============================================
 * <p/>
 * PURPOSE:	Hibernate implementation for Media Storage Service
 * <p/>
 * =============================================
 * MODIFICATION		LOG	DATE		REASON / Change Request #
 * Liu Yang			Jun 22, 2011	Initial file creation
 * =============================================
 */

public class MediaStorageServiceImpl implements MediaStorageService {
    private static Logger logger = LoggerFactory.getLogger(MediaStorageServiceImpl.class.getName());

    @Override
    public void delete(Object obj) {
        if (obj == null)
            return;
        Session se = HibernateUtil.getSession();        
        /*
           * Delete category only if there is no media associated with it
           */
        if (obj.getClass().equals(Media.class)) {
            Media m = (Media) obj;
            List<Category> cats = m.getCategories();
            if (cats != null) {
                for (Category c : cats) {
                    if (c.getMedias().size() == 1 && c.getMedias().get(0).equals(obj) && c.getChildren().size()==0)
                        this.delete(c);
                }
            }
        }
        
        //delete object itself
        se.delete(obj);
    }

    @Override
    public void deleteMediaByReleaseId(long releaseId) {
        Session se = HibernateUtil.getSession();
        se.createQuery("delete from Media where releaseId= " + releaseId).executeUpdate();
    }

    @Override
    public Category getCategory(long id) {
        Session se = HibernateUtil.getSession();
        Category cat = null;
        cat = (Category) se.get(Category.class, id);

        return cat;
    }

    @Override
    public Category getCategoryByFullTitle(String fullTitle) {
        Session se = HibernateUtil.getSession();
        Category cat = null;

        cat = (Category) se.createCriteria(Category.class)
                .add(Restrictions.eq("fullTitle", fullTitle))
                .uniqueResult();
        return cat;
    }

    @Override
    public Media getMedia(long id) {
        Session se = HibernateUtil.getSession();

        Media media = (Media) se.createCriteria(Media.class)
                .add(Restrictions.eq("id", id))
                .uniqueResult();

        return media;
    }

    @Override
    public Media getMediaByReleaseId(long releaseId) {
        Session se = HibernateUtil.getSession();
        Media media = (Media) se.get(Media.class, releaseId);
        return media;
    }

    @Override
    public List<Media> listExpiredMedia(int first, int max) {
        Session se = HibernateUtil.getSession();
        @SuppressWarnings("unchecked")
        List<Media> mediaList = se.createCriteria(Media.class)
                .add(Restrictions.le("mobileExpiryDate", new Date()))
                .add(Restrictions.le("availableInMobile", "true"))
                .setFirstResult(first)
                .setMaxResults(max)
                .list();

        return mediaList;
    }

    @Override
    public List<FailedMedia> listFailedMedia() {
        Session se = HibernateUtil.getSession();

        @SuppressWarnings("unchecked")
        List<FailedMedia> mediaList = se.createCriteria(FailedMedia.class).list();

        return mediaList;
    }

    protected boolean hasDuplicatedSponsors(Sponsor sponsor) {
        Sponsor s = (Sponsor) HibernateUtil.getSession()
                .createCriteria(Sponsor.class)
                .add(Restrictions.eq("imageUrl", sponsor.getImageUrl()))
                .add(Restrictions.eq("url", sponsor.getUrl())).uniqueResult();

        return s == null ? false : true;
    }

    @Override
    public void saveCategory(Category category) {

        Session se = HibernateUtil.getSession();
        if (category.getParent() != null) {
            if (se.get(Category.class, category.getParent().getId()) == null) {
                saveCategory(category.getParent());
            }
        }
        se.saveOrUpdate(category);

    }

    @Override
    public void saveFailedMedia(FailedMedia m) {
        Session se = HibernateUtil.getSession();
        se.saveOrUpdate(m);
    }

    @Override
    public void saveMedia(Media m) {
        //set the cateogry without availableInMobile checked to null

        //if (m.getAvailableInMobile() != null && !m.getAvailableInMobile().equalsIgnoreCase("true")) {
    	if(m.getMobileId() == 0) {
            m.removeAllCategories();
        }


        //save category if exist
        List<Category> categories = m.getCategories();
        if (categories != null) {
            for (Category c : categories) {
                saveCategory(c);
            }
        }

        //save media itself
        HibernateUtil.getSession().saveOrUpdate(m);

    }

    @Override
    public void mergeMedia(Media m) {
        //set the cateogry without availableInMobile checked to null
        //if (m.getAvailableInMobile() != null && !m.getAvailableInMobile().equalsIgnoreCase("true")) {
    	if(m.getMobileId() == 0) {
            m.removeAllCategories();
        }
          Media originalMedia = (Media) get(m.getReleaseId(), Media.class);

        for(Category oldCat : originalMedia.getCategories()){
            if(!m.getCategories().contains(oldCat)){
                deleteIfEmpty(oldCat, m);
            }
        }

        List<Category> categories = m.getCategories();
        if (categories != null) {
            for (Category c : categories) {
                mergeCategory(c);
            }
        }
        HibernateUtil.getSession().merge(m);
    }

    public void mergeCategory(Category category) {
        Session se = HibernateUtil.getSession();
        if (category.getParent() != null) {
            if (se.get(Category.class, category.getParent().getId()) == null) {
                saveCategory(category.getParent());
            }
        }
        se.merge(category);
    }

    @Override
    public void saveOrUpdate(Object obj) {
        HibernateUtil.getSession().saveOrUpdate(obj);
    }

    @Override
    public Object get(long id, @SuppressWarnings("rawtypes") Class c) {
        return HibernateUtil.getSession().get(c, id);
    }

    @Override
    public void save(Object obj) {
        HibernateUtil.getSession().save(obj);
    }

    @Override
    public String runSqlQuery(String query) {
        Session session = HibernateUtil.getSession();
        Connection conn = session.connection();
        Statement stm = null;
        String str = "";
        try {
            stm = conn.createStatement();
            ResultSet rs = null;
            if (query.contains("select"))
                rs = stm.executeQuery(query);
            else
                stm.execute(query);

            if (rs != null) {
                int cols = rs.getMetaData().getColumnCount();
                while (rs.next()) {
                    for (int i = 1; i < cols; i++) {
                        str += rs.getObject(i) + ", ";
                    }
                    str += rs.getObject(cols);
                    str += "\n<br/>\n";
                }
            }
            //conn.commit();
        } catch (SQLException e) {
            logger.error("SQL exception while running query", e);
            try {
                if (conn != null) conn.rollback();
            } catch (SQLException e1) {
                logger.error("SQL exception while running query", e1);
            }
        } finally {
            try {
                if (stm != null) stm.close();
            } catch (SQLException e) {
                logger.error("SQL exception while running query", e);
            }
        }
        return str;
    }

    private void deleteIfEmpty(Category cat, Media m){
        if (cat.getChildren().size()==0 && cat.getMedias().size() == 1 && cat.getMedias().get(0).getReleaseId()==m.getReleaseId()){
            deleteCategory(cat);
        }

    }

    private void deleteCategory(Category cat) {
        Session session = HibernateUtil.getSession();
        if(cat.getParent()!=null){
            cat.getParent().removeChild(cat);
        }
        if(cat.getParent() != null && cat.getParent().getChildren().size() ==0){
            deleteCategory(cat.getParent());
        }
        session.delete(cat);
    }
}
